package com.hp.attendance.dao;



import com.hp.attendance.entity.*;
import com.hp.attendance.util.BaseDAO;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class OneselfDao extends BaseDAO {
    //根据id查询员工信息
    //预修改使用
    public Employee getOneselfOneById(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from employee where id=?";
        Object[] obj = {id};
        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }

    //修改员工信息
    public void UpdateOneself(Employee employee) throws SQLException, IOException, ClassNotFoundException {
        String sql = "update employee set employee_number=?,name=?,password=?," +
                "gender=?,birthday=?,telephone=?,email=?,address=?,education=?,notes=?" +
                " where id=? ";
        Object[] obj ={employee.getEmployee_number(),employee.getName(),employee.getPassword(),
                employee.getGender(),employee.getBirthday(),employee.getTelephone(),
                employee.getEmail(),employee.getAddress(),employee.getEducation(),
                employee.getNotes(),employee.getId()};
        addOrUpdOrDel(sql,obj);
    }

    //根据员工编号查询考勤记录
    public List<Attendance> getOneselfAttendance(int employee_number) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from attendance where employee_number=?";
        Object[] obj = {employee_number};
        List<Attendance> list = queryList(sql, obj, new Attendance());
        return list;
    }

    //请假申请
    public void insertOneselfLeave(Leave leave) throws SQLException, IOException, ClassNotFoundException {
        String sql = "insert into lea values(null,?,?,?,?,?,?,?,null,?,null)";
        Object[] obj = {leave.getEmployee_number(),leave.getDepartment_number(),leave.getStart_time(),
                leave.getEnd_time(),leave.getDays(),leave.getReason(),leave.getType(),leave.getStatus()};
        addOrUpdOrDel(sql,obj);
    }

    //个人请假信息分页查询
    public List<Leave> getOneselfLeaveList(int employeeNumber ,int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from lea where employee_number=? limit ?,?";
        Object[] obj ={employeeNumber,(pageNo-1)*pageSize,pageSize};
        List<Leave> leaves = queryList(sql, obj, new Leave());
        return leaves;
    }

    //查询个人请假数据总条数
    public long getTotal(int employeeNumber) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from lea where employee_number=?";
        Object[] obj = {employeeNumber};
        int count = queryCount(sql, obj);
        return count;
    }

    //查询个人加班信息
    public List<Overtime> getOneselfOvertimeList(int employeeNumber , int pageNo, int pageSize) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select * from overtime where employee_number=? limit ?,?";
        Object[] obj ={employeeNumber,(pageNo-1)*pageSize,pageSize};
        List<Overtime> leaves = queryList(sql, obj, new Overtime());
        return leaves;
    }
    //查询请假时间是否存在
    public List<Leave> getLeaveTime(int employeeNumber , Date startTime, Date endTime) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="SELECT * FROM lea where employee_number=? and end_time>? and start_time<? and status in('未批准','已批准') ";
        Object[] obj ={employeeNumber,startTime,endTime};
        List<Leave> leaves = queryList(sql, obj, new Leave());
        return leaves;
    }

    //查询个人部门加班数据总条数
    public long getOvertimeTotal(int employeeNumber) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select count(1) from overtime where employee_number=?";
        Object[] obj = {employeeNumber};
        int count = queryCount(sql, obj);
        return count;
    }

    //根据部门编号查询部门信息
    public Department getDepartmentByDN(int departmentNumber) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from department where department_number=? ";
        Object[] obj = {departmentNumber};
        Department department = queryOne(sql, obj, new Department());
        return department;
    }

    //根据职位编号查询职位信息
    public Position getPositionByPN(int positionNumber) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from position where position_number=?";
        Object[] obj = {positionNumber};
        Position position = queryOne(sql, obj, new Position());
        return position;
    }

    //根据员工编号查询员工信息
    public Employee getEmployeeByEN(int employeeNumber) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql = "select * from employee where employee_number=?";
        Object[] obj = {employeeNumber};
        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }
    /**
     * 根据id查询个人
     * @param
     * @return
     */
    public Employee getEmployee(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select e.* from overtime o left join employee e on e.employee_number=o.employee_number where o.id=?";
        Object[] obj = {id};
        Employee employee = queryOne(sql, obj, new Employee());
        return employee;
    }

    /**
     * 根据id查询部门
     * @param
     * @return
     */
    public Department getDepartment(int id) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql ="select d.* from overtime o left join department d on d.department_number = o.department_number where o.id=?";
        Object[] obj = {id};
        Department department = queryOne(sql, obj, new Department());
        return department;
    }
}
